package com.singing.dao;

import com.singing.model.Judge;
import com.singing.util.DbUtil;

import java.sql.*;
import java.util.List;

public class JudgeDao {

    // 根据ID查询评委
    public Judge selectById(Integer id) {
        Connection connection = DbUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            ps = connection.prepareStatement("select * from judge where id=?");
            ps.setInt(1, id);
            resultSet = ps.executeQuery();
            Judge judge = null;
            if (resultSet.next()) {
                judge = new Judge();
                judge.setId(resultSet.getInt("id"));
                judge.setName(resultSet.getString("name"));
                judge.setGender(resultSet.getInt("gender"));
            }
            return judge;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeResource(connection, ps, resultSet);
        }
        return null;
    }

    // 添加评委
    public int insert(Judge judge) {
        Connection connection = DbUtil.getConnection();
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(
                    "insert into judge(name, gender) values(?,?)",
                    Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, judge.getName());
            ps.setInt(2, judge.getGender());
            int result = ps.executeUpdate();

            try (ResultSet rs = ps.getGeneratedKeys()) {
                if (rs.next()) {
                    int generatedId = rs.getInt(1);
                    judge.setId(generatedId);
                }
            }
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeResource(connection, ps);
        }
        return 0;
    }

    // 更新评委信息
    public int update(Judge judge) {
        Connection connection = DbUtil.getConnection();
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(
                    "update judge set name=?, gender=? where id=?");
            ps.setString(1, judge.getName());
            ps.setInt(2, judge.getGender());
            ps.setInt(3, judge.getId());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeResource(connection, ps);
        }
        return 0;
    }

    // 删除评委
    public int deleteById(Integer id) {
        Connection connection = DbUtil.getConnection();
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("delete from judge where id=?");
            ps.setInt(1, id);
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeResource(connection, ps);
        }
        return 0;
    }

    // 查询所有评委,返回list
    public List<Judge> selectAll() {
        Connection connection = DbUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            ps = connection.prepareStatement("select * from judge");
            resultSet = ps.executeQuery();
            java.util.ArrayList<Judge> list = new java.util.ArrayList<>();
            while (resultSet.next()) {
                Judge judge = new Judge();
                judge.setId(resultSet.getInt("id"));
                judge.setName(resultSet.getString("name"));
                judge.setGender(resultSet.getInt("gender"));
                list.add(judge);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeResource(connection, ps, resultSet);
        }
        return null;
    }
}